package www.qiyue.sm;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import www.qiyue.utils.UUIDGenerator;

import java.nio.Buffer;
import java.util.*;

@RestController
public class bgsm {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @RequestMapping("sm")
    public void test(){
        String sql="SELECT * from testrent";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        for (Map<String, Object> map : list) {
            String rentcode = map.get("rentcode").toString();
            String codename = map.get("codename").toString();
            String rentSql="SELECT * from ais_rentunit WHERE rent_unit_code='"+rentcode+"'";
            List<Map<String, Object>> rentList = jdbcTemplate.queryForList(rentSql);
            String smSql="SELECT * from ais_certificatee WHERE house_id=";
            List<Map<String, Object>> forList = jdbcTemplate.queryForList(smSql);
            String certificatee_code=null;
            if (forList.size()>0&&forList!=null){
                certificatee_code=forList.get(0).get("certificatee_code")+"";

            }
            StringBuilder stringBuilder = new StringBuilder();
            for (int i = 0; i < rentList.size(); i++) {
                String id = rentList.get(i).get("id") + "";

                if (i==rentList.size()-1){
                    stringBuilder.append(id);
                }else {
                    stringBuilder.append(id+",");
                }
            }
            String gly="SELECT * from ais_glykz WHERE network_admin='"+codename+"'";

            List<Map<String, Object>> glyList = jdbcTemplate.queryForList(gly);
            if (glyList.size()==1){
                String network_admin = glyList.get(0).get("network_admin").toString();
                String network_admin_id = glyList.get(0).get("network_admin_id").toString();
                String network_admin_account = glyList.get(0).get("network_admin_account").toString();
                String block_length = glyList.get(0).get("block_length").toString();
                String block_length_id = glyList.get(0).get("block_length_id").toString();
                String block_length_code = glyList.get(0).get("block_length_code").toString();
                String ids = stringBuilder.toString();
                String rentIds = ids.replace(",", "','");
                String updateRent="UPDATE ais_rentunit set network_admin='"+network_admin+"',network_admin_id='"+network_admin_id+"',network_admin_account='"+network_admin_account+"',block_length='"+block_length+"',block_length_id='"+block_length_id+"',block_length_code='"+block_length_code+"' WHERE id in ('"+rentIds+"')";
                jdbcTemplate.update(updateRent);
                String sqlx=",a.block_length=b.block_length,a.block_length_id=b.block_length_id,a.block_length_code=b.block_length_code";
                String conSql = (new StringBuilder("UPDATE ais_contract a INNER JOIN ais_rentunit  b on a.rent_unit_id = b.id set a.network_admin = b.network_admin ,a.network_admin_account= b.network_admin_account,a.network_admin_id=b.network_admin_id "+sqlx+" where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(conSql);
                String planSql = (new StringBuilder("UPDATE ais_feeplan a INNER JOIN ais_rentunit b on a.rent_unit_id = b.id set a.network_admin = b.network_admin,a.network_admin_account=b.network_admin_account,a.network_admin_id = b.network_admin_id "+sqlx+" where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(planSql);
                String billSql = (new StringBuilder("UPDATE ais_billing a INNER JOIN ais_rentunit b on a.rent_unit_id = b.id  set a.network_admin = b.network_admin,a.network_admin_account=b.network_admin_account,a.network_admin_id=b.network_admin_id "+sqlx+" where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(billSql);
                String feeSql = (new StringBuilder("UPDATE ais_fee a INNER JOIN ais_rentunit b on a.rent_unit_id = b.id set a.network_admin = b.network_admin ,a.network_admin_account = b.network_admin_account ,a.network_admin_id=b.network_admin_id "+sqlx+" where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(feeSql);
                String metSql = (new StringBuilder("UPDATE ais_meter a INNER JOIN ais_rentunit b on a.rent_unit_id = b.id  set a.network_admin = b.network_admin,a.network_admin_account = b.network_admin_account where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(metSql);
                String recordSql = (new StringBuilder("UPDATE ais_meter_reading a INNER JOIN ais_rentunit b on a.rent_unit_id = b.id  set a.network_admin = b.network_admin,a.network_admin_account = b.network_admin_account ,a.block_length_code=b.block_length_code where a.rent_unit_id in ('")).append(rentIds).append("')").toString();
                jdbcTemplate.update(recordSql);
            }else {
                System.out.println("glyList = " + gly);
            }
        }
    }

    @RequestMapping("smtest")
    public String smtest(){
        String sql=
                "SELECT * from  cgform_field WHERE table_id='2c83b38b5d177418015d2a603e85008c'";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

        String [] testList={"id", "content", "create_by", "create_date", "create_name", "dict_field", "dict_table", "dict_text", "field_default", "field_href", "field_length", "field_name", "field_valid_type", "is_key", "is_null", "is_query", "is_show", "is_show_list", "length", "main_field", "main_table", "old_field_name", "order_num", "point_length", "query_mode", "show_type", "type", "update_by", "update_date", "update_name", "table_id", "extend_json"};
        for (Map<String, Object> map : list) {
            StringBuilder stringBuilder = new StringBuilder();
            StringBuilder vuBuilder = new StringBuilder();
            stringBuilder.append("INSERT INTO `sm`.`cgform_field`(");
            String generate = UUIDGenerator.generate();
            map.put("id", generate);
            map.put("table_id","40287c81754a977901754b0ee13500a7");
            map.put("update_date","2020-10-22");
            for (int i = 0; i < testList.length; i++) {
                String key = testList[i];

                if (i==testList.length-1){
                    stringBuilder.append(key+ " ) VALUES ( ");
                    vuBuilder.append("'"+map.get(key)+"' )");
                }else {
                    stringBuilder.append(key+",");
                    vuBuilder.append("'"+map.get(key)+"',");
                }
            }
           //ld`, `dict_table`, `dict_text`, `field_default`, `field_href`, `field_length`, `field_name`, `field_valid_type`, `is_key`, `is_null`, `is_query`, `is_show`, `is_show_list`, `length`, `main_field`, `main_table`, `old_field_name`, `order_num`, `point_length`, `query_mode`, `show_type`, `type`, `update_by`, `update_date`, `update_name`, `table_id`, `extend_json`) VALUES ('2c83b38b5d177418015d2a603e85008d', '主键', 'admin', '2017-07-10 10:42:17', '管理员', '', '', '', '', '', 120, 'id', '', 'Y', 'N', 'N', 'N', 'N', 36, '', '', 'id', 1, 0, 'single', 'text', 'string', NULL, NULL, NULL, '2c83b38b5d177418015d2a603e85008c', '')";
            System.out.println(stringBuilder.toString()+vuBuilder.toString());
            jdbcTemplate.update(stringBuilder.toString()+vuBuilder.toString());

        }
        return "haoel";
    }

    @RequestMapping("notifys")
    public void notifys(){

        System.out.println("-----------------------------------执行合同结束流程监听---------------------");
        String bpmTitle="ais_rentunit_change";
        String id="2c83b3a96f470058016f54bdcae80063";
        String sql = "SELECT * from " + bpmTitle + " WHERE id='" + id + "'";
        List<Map<String,Object>> columList=null;
        String table_name = "";
        Map<String, Object> forMap = new HashMap<String, Object>();
        try {
            table_name = "ais_" + bpmTitle.split("_")[1];

            String column="select COLUMN_NAME from information_schema.COLUMNS where table_name = '"+table_name+"' and table_schema = 'sm'";
            if("ais_rentunit".equals(table_name)){
                column="select COLUMN_NAME from information_schema.COLUMNS where table_name = '"+bpmTitle+"' and table_schema = 'sm'";
            }
            forMap = jdbcTemplate.queryForMap(sql);
            columList=jdbcTemplate.queryForList(column);
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("查询执行失败");
        }

        if (forMap != null) {
            if("ais_rentunit".equals(table_name)){
                Iterator<Map<String, Object>> productsIterator = columList.iterator();
                while (productsIterator.hasNext()) {
                    //删除特定元素
                    Map<String, Object> product = productsIterator.next();
                    if("files".equals(product.get("COLUMN_NAME").toString())){
                        productsIterator.remove();
                    }
                }
            }
            String change_id = forMap.get("change_id") == null ? null : forMap.get("change_id").toString();
            String create_name = forMap.get("create_name") == null ? null : forMap.get("create_name").toString();
            String create_by = forMap.get("create_by") == null ? null : forMap.get("create_by").toString();
            String sys_org_code = forMap.get("sys_org_code") == null ? null : forMap.get("sys_org_code").toString();
            if (change_id != null) {
                forMap.remove("change_id");
                forMap.remove("id");
                forMap.remove("create_name");
                forMap.remove("create_by");
                forMap.remove("create_date");
                forMap.remove("update_name");
                forMap.remove("update_date");
                forMap.remove("update_by");
                forMap.remove("sys_company_code");
                forMap.remove("bpm_status");
            }

            Set<String> strings = forMap.keySet();
            Set<String> keys = new HashSet<>();
            for (Map<String, Object> objectMap : columList) {
                for (String string : strings) {
                    if (objectMap.get("COLUMN_NAME").toString().equals(string)) {
                        keys.add(string);
                    }
                }
            }


            StringBuffer buf = new StringBuffer();
            String sqls = "UPDATE " + table_name + " set ";
            buf.append(sqls);

            for (String s : keys) {
                if (forMap.get(s) != null) {
                    buf.append(" " + s + " = '" + forMap.get(s) + "' , ");
                }else{
                    buf.append(" " + s + " = '' , ");
                }
            }
            if(!"ais_rentunit".equals(table_name)){
                if (create_name != null && create_by != null) {
                    buf.append(" update_name = '" + create_name + "'  ,update_name = '" + create_by
                            + "' ,update_date=now()  ");
                } else {
                    buf.append(" update_date=now()  ");
                }
            }else {
                buf.append(" sys_org_code_s="+sys_org_code+"  ");
            }

            String byid = "  WHERE id='" + change_id + "'";

            buf.append(byid);
            System.out.println(buf.toString());
            jdbcTemplate.update(buf.toString());
        }

    }
}
